﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace proRFID_DiemDanh.SinhVien
{
    public partial class ThemSVExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        static DataTable DocExcel2(string path)
        {
            try
            {
                OleDbConnection conn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'", path));
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                da.SelectCommand = new OleDbCommand("select * from [Sheet1$]", conn);
                da.Fill(ds, "table");
                return ds.Tables["table"];
            }
            catch (Exception)
            {
                DataTable dt = new DataTable();
                dt = null;
                return dt;
            }
        }
        bool kiemtra(string id)
        {
            DataTable dt1 = new DataTable();
            dt1 = mdSinhVien.sinhvien_select("0");
            for (int j = 0; j < dt1.Rows.Count; j++)
                if (Convert.ToInt32(id.CompareTo(dt1.Rows[j]["MASV"].ToString().Trim())) == 0)                
                    return true;
            return false;
        }
       
        void luudulieu(DataTable dt)
        {

            int rows = dt.Rows.Count;
            int cols = dt.Columns.Count;

            DataTable dt1 = new DataTable("DSTRUNG");
            DataColumn dc1 = new DataColumn("MASVT");
            DataColumn dc2 = new DataColumn("HOSVT");
            DataColumn dc3 = new DataColumn("TENSVT");
            DataColumn dc4 = new DataColumn("MATHET");
            dt1.Columns.Add(dc1);
            dt1.Columns.Add(dc2);
            dt1.Columns.Add(dc3);
            dt1.Columns.Add(dc4);
            DataRow dr;
            int i;
            for (i = 0; i < rows; i++)
            {
                if (kiemtra(Convert.ToString(dt.Rows[i][0].ToString())) == true)
                {
                   
                    dr = dt1.NewRow();
                    dr[0] = Convert.ToString(dt.Rows[i][0].ToString());
                    dr[1] = dt.Rows[i][1].ToString();
                    dr[2] = dt.Rows[i][2].ToString();
                    dr[3] = dt.Rows[i][3].ToString();
                    dt1.Rows.Add(dr);                     
                }
                else
                {
                    mdSinhVien.sinhvien_insert(dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(),dt.Rows[i][2].ToString(),dt.Rows[i][3].ToString());
                }

            }
            rpt_danhsach.DataSource = mdSinhVien.sinhvien_select("0");
            rpt_danhsach.DataBind();
            rpt_danhsachtrung.DataSource = dt1;
            rpt_danhsachtrung.DataBind();          
                
        }

        protected void cmdThem_Click(object sender, ImageClickEventArgs e)
        {
            if (FU_chonfile.HasFile)
            {
                string DefaultFileName = "upload/";
                string FilePath = Server.MapPath(DefaultFileName) + FU_chonfile.FileName;
                FU_chonfile.SaveAs(FilePath);
                DataTable dt = DocExcel2(FilePath);
                if (dt == null)
                {
                    lbl_thongbao.Text = "File excel đã bị lỗi hoặc sai định dạng.";
                    return;
                }
                luudulieu(dt);
            }
        }
    }
}